﻿using OfficeOpenXml;

//#nullable disable
namespace Common
{
    /// <summary>
    /// excel 工具类
    /// </summary>
    public class ExcelTool
    {
        public readonly Dictionary<string, string> ParamList = new Dictionary<string, string>();

        /// <summary>
        /// 读取参数
        /// </summary>
        /// <param name="key">参数名</param>
        /// <returns>参数值</returns>
        /// <exception cref="Exception"></exception>
        public string GetParams(string key)
        {
            if (ParamList.Keys.Contains(key))
            {
                return ParamList[key];
            }
            throw new Exception($"未找到[{key}]配置项");
        }

        /// <summary>
        /// 解析文件
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="sheetName">sheet名称</param>
        /// <exception cref="FileNotFoundException"></exception>
        public void ParseFile(string path, string sheetName)
        {
            ExcelPackage.License.SetNonCommercialOrganization("BoYao");
            if (!File.Exists(path))
            {
                throw new FileNotFoundException($"{path} file not found");
            }

            using ExcelPackage package = new ExcelPackage(path);
            ExcelWorksheet sheet = package.Workbook.Worksheets[sheetName];

            int row = 1;
            while (true)
            {
                ExcelRangeBase cell1 = sheet.Cells[row, 1];
                ExcelRangeBase cell2 = sheet.Cells[row, 2];
                if (cell1.Value == null)
                {
                    break;
                }
                if (string.IsNullOrEmpty(cell1?.Value?.ToString()?.Trim(' ')))
                {
                    break;
                }
                string key = cell1?.Value?.ToString()?.Trim(' ') ?? string.Empty;

                string value = cell2?.Value?.ToString()?.Trim(' ') ?? string.Empty;

                ParamList.Add(key, value);
                row++;
            }
        }

        /// <summary>
        /// 保存配置到文件
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="sheetName">sheet名</param>
        /// <exception cref="FileNotFoundException"></exception>
        public void SaveToFile(string path, string sheetName)
        {
            if (ParamList is null)
            {
                throw new ArgumentNullException(nameof(ParamList));
            }
            if (!File.Exists(path))
            {
                throw new FileNotFoundException(path);
            }

            ExcelPackage.License.SetNonCommercialOrganization("BoYao");

            using ExcelPackage package = new ExcelPackage(path);
            ExcelWorksheet sheet = package.Workbook.Worksheets[sheetName];

            if (sheet == null)
            {
                throw new Exception($"sheet[{sheetName}] Not Found");
            }

            for (int row = 0; row < ParamList.Count; row++)
            {
#pragma warning disable CS8602
                string key = sheet.Cells[row + 1, 1]?.Value?.ToString() ?? string.Empty;
                sheet.Cells[row + 1, 2].Value = ParamList[key];
#pragma warning restore CS8602
            }

            package.Save();
        }
    }
}